﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Jungle.Code.DAL;
using Jungle.Code.Utils;
using System.Data.SqlClient;
using System.Data;

namespace Jungle.Code.BookManager
{
    public class Book
    {
        public string err;
        private static string ConnectionString = DBHelper.ConnectionString;

        public List<Book> BooksList;

        #region Fields

        private int id; // идентификатор
        private string title; //название
        private string author; // автор
        private string publisher; // издательство 
        private int pubYear; // год публикации
        private int pagesCount; // число страниц
        private string format; // форматы
        private double sizeMb; // размер файлов в соответственных форматах
        private string quality; // качество
        private string series; // серия 
        private string language; // язык
        private string imagePath; // ссылка на фотку
        private string description; // описание
        private int categoryId; // йади каталога
        private double price; //цена
        private string downloadPath; // ссылка для скачивания
        private bool visible; // видна ли в каталоге

        #endregion

        #region Properies

        public int Id { get { return id; } set { id = value; } }
        public string Title { get { return title; } set { title = value; } }
        public string Author { get { return author; } set { author = value; } }
        public string Publisher { get { return publisher; } set { publisher = value; } }
        public int PubYear { get { return pubYear; } set { pubYear = value; } }
        public int PagesCount { get { return pagesCount; } set { pagesCount = value; } }
        public string Format { get { return format; } set { format = value; } }
        public double SizeMb { get { return sizeMb; } set { sizeMb = value; } }
        public string Quality { get { return quality; } set { quality = value; } }
        public string Series { get { return series; } set { series = value; } }
        public string Language { get { return language; } set { language = value; } }
        public string ImagePath { get { return imagePath; } set { imagePath = value; } }
        public string Description { get { return description; } set { description = value; } }
        public int CategoryId { get { return categoryId; } set { categoryId = value; } }
        public double Price { get { return price; } set { price = value; } }
        public string DownloadPath { get { return downloadPath; } set { downloadPath = value; } }
        public bool Visible { get { return visible; } set { visible = value; } }

        #endregion

        //---------------------------------------------------------------------------------
        public Book()
        {
            Title = "(не задан)";
            Author = "(не задан)";
            Publisher = "(не задан)";
            PubYear = 2000;
            PagesCount = 0;
            Format = "(не задан)";
            SizeMb = 0;
            Quality = "(не задан)";
            Series = "(не задан)";
            Language = "(не задан)";
            ImagePath = "no_photo";
            Description = "(не задан)";
            CategoryId = 0;
            Price = 0;
            DownloadPath = "(не задан)";
            Visible = true;
        }
        //---------------------------------------------------------------------------------
        public Book(string titleValue)
        {
            Title = titleValue;
            Author = "(не задан)";
            Publisher = "(не задан)";
            PubYear = 2000;
            PagesCount = 0;
            Format = "(не задан)";
            SizeMb = 0;
            Quality = "(не задан)";
            Series = "(не задан)";
            Language = "(не задан)";
            ImagePath = "no_photo";
            Description = "(не задан)";
            CategoryId = 0;
            Price = 0;
            DownloadPath = "(не задан)";
            Visible = true;
        }
        //---------------------------------------------------------------------------------
        public Book(int Id)
        {
            GetById(Id);
        }
        //---------------------------------------------------------------------------------
        public bool Add()
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {
                   
                    SqlCommand command = new SqlCommand("catalog.Book_Add", connect);                   

                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@Title", SqlDbType.VarChar).Value = Title;
                    command.Parameters.Add("@Author", SqlDbType.VarChar).Value = Author;
                    command.Parameters.Add("@Publisher", SqlDbType.VarChar).Value = Publisher;
                    command.Parameters.Add("@PubYear", SqlDbType.Int).Value = PubYear;
                    command.Parameters.Add("@PagesCount", SqlDbType.Int).Value = PagesCount;
                    command.Parameters.Add("@Format", SqlDbType.VarChar).Value = Format;
                    command.Parameters.Add("@SizeMb", SqlDbType.Float).Value = SizeMb;
                    command.Parameters.Add("@Quality", SqlDbType.VarChar).Value = Quality;
                    command.Parameters.Add("@Series", SqlDbType.VarChar).Value = Series;
                    command.Parameters.Add("@Language", SqlDbType.VarChar).Value = Language;
                    command.Parameters.Add("@ImagePath", SqlDbType.VarChar).Value = ImagePath;
                    command.Parameters.Add("@Description", SqlDbType.VarChar).Value = Description;
                    command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;
                    command.Parameters.Add("@Price", SqlDbType.Float).Value = Price;
                    command.Parameters.Add("@DownloadPath", SqlDbType.VarChar).Value = DownloadPath;
                    command.Parameters.Add("@Visible", SqlDbType.Bit).Value = Visible;

                    command.Connection.Open();

                    command.ExecuteNonQuery();

                    command.Connection.Close();

                }
                return true;
            }
            catch
            {
                return false;
            }

        }
        //---------------------------------------------------------------------------------
        public bool GetById(int Id)
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {
                    
                    SqlCommand command = new SqlCommand("catalog.Book_GetById", connect);
                    SqlDataReader reader = null;

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@Id", SqlDbType.Int).Value = Id;

                    command.Connection.Open();
                    reader = command.ExecuteReader();

                    if (reader == null) { return false; }

                    while (reader.Read())
                    {
                        Title = reader["Title"].ToString().Trim();
                        Author = reader["Author"].ToString().Trim();
                        Publisher = reader["Publisher"].ToString().Trim();
                        PubYear = (int)reader["PubYear"];
                        PagesCount = (int)reader["PagesCount"];
                        Format = reader["Format"].ToString().Trim();
                        SizeMb = (double)reader["SizeMb"];
                        Quality = reader["Quality"].ToString().Trim();
                        Series = reader["Series"].ToString().Trim();
                        Language = reader["Language"].ToString().Trim();
                        ImagePath = reader["ImagePath"].ToString().Trim();
                        Description = reader["Description"].ToString().Trim();
                        CategoryId = (int)reader["CategoryId"];
                        Price = (double)reader["Price"];
                        DownloadPath = reader["DownloadPath"].ToString().Trim();
                        Visible = (bool)reader["Visible"];
                    }

                    reader.Close();

                    command.Connection.Close();


                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        //---------------------------------------------------------------------------------
        public int GetAll()
        {
            try
            {
                int count = 0;

                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {
                    BooksList = new List<Book>();
                    
                    SqlCommand command = new SqlCommand("catalog.Book_GetAll", connect);
                    SqlDataReader reader = null;

                    command.CommandType = CommandType.StoredProcedure;                  
                    
                    command.Connection.Open();
                    reader = command.ExecuteReader();

                    if (reader == null) { return 0; }

                    while (reader.Read())
                    {
                        Book book = new Book();
                        book.id = (int)reader["Id"];
                        book.Title = reader["Title"].ToString().Trim();
                        book.Author = reader["Author"].ToString().Trim();
                        book.Publisher = reader["Publisher"].ToString().Trim();
                        book.PubYear = (int)reader["PubYear"];
                        book.PagesCount = (int)reader["PagesCount"];
                        book.Format = reader["Format"].ToString().Trim();
                        book.SizeMb = (double)reader["SizeMb"];
                        book.Quality = reader["Quality"].ToString().Trim();
                        book.Series = reader["Series"].ToString().Trim();
                        book.Language = reader["Language"].ToString().Trim();
                        book.ImagePath = reader["ImagePath"].ToString().Trim();
                        book.Description = reader["Description"].ToString().Trim();
                        book.CategoryId = (int)reader["CategoryId"];
                        book.Price = (double)reader["Price"];
                        book.DownloadPath = reader["DownloadPath"].ToString().Trim();
                        book.Visible = (bool)reader["Visible"];

                        BooksList.Add(book);
                        count++;
                    }

                    reader.Close();

                    command.Connection.Close();


                }
                return count;
            }
            catch
            {
                return 0;
            }
        }
        //---------------------------------------------------------------------------------
        public void Delete()
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {

                    SqlCommand command = new SqlCommand("catalog.Book_Delete", connect);

                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@Id", SqlDbType.VarChar).Value = Id;
                    
                    command.Connection.Open();

                    command.ExecuteNonQuery();

                    command.Connection.Close();

                }                
            }
            catch (Exception ee)
            {
                err = ee.ToString();
            }
        }
        //---------------------------------------------------------------------------------
        public void Update()
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {

                    SqlCommand command = new SqlCommand("catalog.Book_Update", connect);

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
                    command.Parameters.Add("@Title", SqlDbType.VarChar).Value = Title;
                    command.Parameters.Add("@Author", SqlDbType.VarChar).Value = Author;
                    command.Parameters.Add("@Publisher", SqlDbType.VarChar).Value = Publisher;
                    command.Parameters.Add("@PubYear", SqlDbType.Int).Value = PubYear;
                    command.Parameters.Add("@PagesCount", SqlDbType.Int).Value = PagesCount;
                    command.Parameters.Add("@Format", SqlDbType.VarChar).Value = Format;
                    command.Parameters.Add("@SizeMb", SqlDbType.Float).Value = SizeMb;
                    command.Parameters.Add("@Quality", SqlDbType.VarChar).Value = Quality;
                    command.Parameters.Add("@Series", SqlDbType.VarChar).Value = Series;
                    command.Parameters.Add("@Language", SqlDbType.VarChar).Value = Language;
                    command.Parameters.Add("@ImagePath", SqlDbType.VarChar).Value = ImagePath;
                    command.Parameters.Add("@Description", SqlDbType.VarChar).Value = Description;
                    command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;
                    command.Parameters.Add("@Price", SqlDbType.Float).Value = Price;
                    command.Parameters.Add("@DownloadPath", SqlDbType.VarChar).Value = DownloadPath;
                    command.Parameters.Add("@Visible", SqlDbType.Bit).Value = Visible;

                    command.Connection.Open();

                    command.ExecuteNonQuery();

                    command.Connection.Close();

                }                
            }
            catch (Exception ee)
            {
                err = ee.ToString();
            }
        }
        //---------------------------------------------------------------------------------
        public int GetHidden()
        {
            try
            {
                int count = 0;

                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {
                    BooksList = new List<Book>();

                    SqlCommand command = new SqlCommand("catalog.Book_GetAllHidden", connect);
                    SqlDataReader reader = null;

                    command.CommandType = CommandType.StoredProcedure;

                    command.Connection.Open();
                    reader = command.ExecuteReader();

                    if (reader == null) { return 0; }

                    while (reader.Read())
                    {
                        Book book = new Book();
                        book.id = (int)reader["Id"];
                        book.Title = reader["Title"].ToString().Trim();
                        book.Author = reader["Author"].ToString().Trim();
                        book.Publisher = reader["Publisher"].ToString().Trim();
                        book.PubYear = (int)reader["PubYear"];
                        book.PagesCount = (int)reader["PagesCount"];
                        book.Format = reader["Format"].ToString().Trim();
                        book.SizeMb = (double)reader["SizeMb"];
                        book.Quality = reader["Quality"].ToString().Trim();
                        book.Series = reader["Series"].ToString().Trim();
                        book.Language = reader["Language"].ToString().Trim();
                        book.ImagePath = reader["ImagePath"].ToString().Trim();
                        book.Description = reader["Description"].ToString().Trim();
                        book.CategoryId = (int)reader["CategoryId"];
                        book.Price = (double)reader["Price"];
                        book.DownloadPath = reader["DownloadPath"].ToString().Trim();
                        book.Visible = (bool)reader["Visible"];

                        BooksList.Add(book);
                        count++;
                    }

                    reader.Close();

                    command.Connection.Close();


                }
                return count;
            }
            catch
            {
                return 0;
            }
        }
        //---------------------------------------------------------------------------------
        public int GetUnHidden()
        {
            try
            {
                int count = 0;

                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {
                    BooksList = new List<Book>();

                    SqlCommand command = new SqlCommand("catalog.Book_GetAllUnHidden", connect);
                    SqlDataReader reader = null;

                    command.CommandType = CommandType.StoredProcedure;

                    command.Connection.Open();
                    reader = command.ExecuteReader();

                    if (reader == null) { return 0; }

                    while (reader.Read())
                    {
                        Book book = new Book();
                        book.id = (int)reader["Id"];
                        book.Title = reader["Title"].ToString().Trim();
                        book.Author = reader["Author"].ToString().Trim();
                        book.Publisher = reader["Publisher"].ToString().Trim();
                        book.PubYear = (int)reader["PubYear"];
                        book.PagesCount = (int)reader["PagesCount"];
                        book.Format = reader["Format"].ToString().Trim();
                        book.SizeMb = (double)reader["SizeMb"];
                        book.Quality = reader["Quality"].ToString().Trim();
                        book.Series = reader["Series"].ToString().Trim();
                        book.Language = reader["Language"].ToString().Trim();
                        book.ImagePath = reader["ImagePath"].ToString().Trim();
                        book.Description = reader["Description"].ToString().Trim();
                        book.CategoryId = (int)reader["CategoryId"];
                        book.Price = (double)reader["Price"];
                        book.DownloadPath = reader["DownloadPath"].ToString().Trim();
                        book.Visible = (bool)reader["Visible"];

                        BooksList.Add(book);
                        count++;
                    }

                    reader.Close();

                    command.Connection.Close();


                }
                return count;
            }
            catch
            {
                return 0;
            }
        }
        //---------------------------------------------------------------------------------
        public static bool HideUnHide(int Id, bool Visibility)
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(ConnectionString))
                {

                    SqlCommand command = new SqlCommand("catalog.Book_HideUnhide", connect);

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
                    command.Parameters.Add("@Visible", SqlDbType.Bit).Value = Visibility;

                    command.Connection.Open();

                    command.ExecuteNonQuery();

                    command.Connection.Close();

                }
                return true;
            }
            catch (Exception ee)
            {
                return false;
            }
        }
        //---------------------------------------------------------------------------------
        
   }
}
